﻿using System;
using System.Data;
using System.Data.SqlClient;

namespace UniversityStudentManagemnetSystem.DAO.DAL.Gateway
{
    public class TeacherGateway:DAO.Gateway.Gateway
    {
        Teacher aTeacher=new Teacher();
        public bool TeacherSave(Teacher teacher)
        {
            try
            {
                Connection.Open();
                string insertQuery =
                    string.Format(
                        "insert into t_Teacher values (@Name,@Email,@Address,@ContactNo,@DesignationId,@DepartmentId,@Credit)");
                Command.CommandText = insertQuery;
                Command.Parameters.Clear();
                Command.Parameters.Add("@Name", SqlDbType.VarChar);
                Command.Parameters["@Name"].Value = teacher.TeacherName;
                Command.Parameters.Add("@Email", SqlDbType.VarChar);
                Command.Parameters["@Email"].Value = teacher.Email;
                Command.Parameters.Add("@Address", SqlDbType.VarChar);
                Command.Parameters["@Address"].Value = teacher.Address;
                Command.Parameters.Add("@ContactNo", SqlDbType.VarChar);
                Command.Parameters["@ContactNo"].Value = teacher.ContactNumber;
                Command.Parameters.Add("@DesignationId", SqlDbType.Int);
                Command.Parameters["@DesignationId"].Value = teacher.Designation.DesignationId;
                Command.Parameters.Add("@DepartmentId", SqlDbType.Int);
                Command.Parameters["@DepartmentId"].Value = teacher.Department.DeptId;
                Command.Parameters.Add("@Credit", SqlDbType.VarChar);
                Command.Parameters["@Credit"].Value = teacher.Credit;
                Command.ExecuteNonQuery();
            }
            catch(Exception exception)
            {
               throw new Exception("Save couldn't performed due to system error!", exception);
            }
            finally
            {
                if (Connection!=null && Connection.State==ConnectionState.Open)
                {
                    Connection.Close(); 
                }
            }
            return true;
        }

        public bool HasThisEmail(string email)
        {
            try
            {
                Connection.Open();
                string query = String.Format("SELECT * FROM t_Teacher WHERE Email=@email");
                Command.CommandText = query;
                Command.Parameters.Clear();
                Command.Parameters.Add("@email", SqlDbType.VarChar);
                Command.Parameters["@email"].Value = email;
                SqlDataReader reader = Command.ExecuteReader();
                bool hasRows = false;
                if (reader != null)
                {
                    hasRows = reader.HasRows;
                    return hasRows;
                }
            }
            catch (Exception exception)
            {

                throw new Exception("System error!", exception);
            }
            finally
            {
                if (Connection != null && Connection.State == ConnectionState.Open)
                {
                    Connection.Close();
                }
            }
            return true;
        }

        public DataTable GetAllTeacherFor(int selectedDepartmentId)
        {
            Command.CommandText = "SELECT * FROM t_Teacher WHERE departmentId = @deptId";
            Command.Parameters.Clear();
            Command.Parameters.Add("@deptId", SqlDbType.Int).Value = selectedDepartmentId;
            queryString = GetQueryString(Command);

            DataSet dataSet = new DataSet();
            SqlDataAdapter dataAdapter = new SqlDataAdapter(queryString, Connection.ConnectionString);
            dataAdapter.Fill(dataSet);
            DataTable dataTable = dataSet.Tables[0];

            nbrOfTableRows = dataTable.Rows.Count;
            return dataTable;
        }

        public DataTable GetAllTeachers()
        {
            string selectQuery = string.Format("select * from t_Teacher");
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(selectQuery, GetConnectionString());
            DataSet dataSet = new DataSet();
            sqlDataAdapter.Fill(dataSet, "VcTable");
            DataTable myDataTable = dataSet.Tables["VcTable"];
            return myDataTable;
        }

        public Teacher GetTeacherPersonalInformation(string nameOrEmail)
        {
            try
            {
                Connection.Open();
                string selectQuery =
                    string.Format("Select * from ViewTeacherDepartment where Name='" + nameOrEmail + "' or Email='" + nameOrEmail +
                                  "'");
                Command.CommandText = selectQuery;
                SqlDataReader reader = Command.ExecuteReader();
                while (reader.Read())
                {
                    aTeacher.TeacherName = reader["Name"].ToString();
                    aTeacher.Email = reader["Email"].ToString();
                    aTeacher.Address = reader["Address"].ToString();
                    aTeacher.ContactNumber = reader["ContactNo"].ToString();
                    aTeacher.Department.Name = reader["Department"].ToString();
                    aTeacher.Designation.DesignationName = reader["Designation"].ToString();



                }
            }
            catch (Exception exception)
            {
                
                throw new Exception("Sorry!System Error!Please Try Again!",exception);
            }
            finally
            {
                if (Connection!=null && Connection.State==ConnectionState.Open)
                {
                    Connection.Close();
                }
            }
            return aTeacher;
        }
    }
}